from sqlite3 import connect
from optparse import OptionParser
import sys
from json import loads
from sched_db.create import create_tables, delete_tables
from sched_db.importlib import *

parser = OptionParser()
parser.add_option("-i", dest="srcdbfile", help="Input database file name")
parser.add_option("-s", dest="semester", help="Semester")
(opt, args) = parser.parse_args()
if not (opt.srcdbfile and opt.semester):
  parser.print_usage()
  sys.exit(0)

semester = opt.semester
dbfile = "%s.sqlite" % opt.semester

delete_tables(dbfile)
create_tables(dbfile)

db0 = connect(opt.srcdbfile)
c0 = db0.cursor()

db = connect(dbfile)
c = db.cursor()

# Courses
c0.execute("select code, title from courses")
for row in map(list, c0.fetchall()):
  c.execute("insert or ignore into Course values(?,?)",
    (row[0].upper(), row[1]))
db.commit()

# Section
c0.execute("""
  select sec_number, sec_code, course, semester, capacity, actual
  from sections
  where semester=?""", (semester,))
for row in map(list, c0.fetchall()):
  row[2] = row[2].upper()
  c.execute("""
    insert into Section(sec, crn, code, semester, maxsize, actsize)
                 values(?, ?, ?, ?, ?, ?)
    """, row)
db.commit()

# Schedule
c0.execute("""
  select sec_number, course, semester, schedtype, campus, location,
         day, hour_start, min_start, hour_end, min_end 
         from sections S join schedules 
              on section_id = S.id 
         where semester =?""", (semester,))
for row in c0.fetchall():
  sec = row[0]
  code = row[1].upper()
  semester = row[2]
  schd = row[3]
  campus = row[4]
  location = row[5].split()[-1].upper()
  day = row[6].upper()
  try:
    btime = "%.2d%.2d" % (row[7], row[8])
  except:
    continue
  try:
    etime = "%.2d%.2d" % (row[9], row[10])
  except:
    continue
  c.execute("""
  insert or ignore into Schedule(sec,code,semester,schd,campus,location,day,btime,etime)
                values(?,  ?,   ?,       ?,   ?,     ?,       ?,  ?,    ?)
  """, (sec, code, semester, schd, campus, location, day, btime, etime))
db.commit()

# Room
c.execute("""
  insert into Room(campus, location, capacity)
    select campus, location, max(maxsize)
    from Section natural join Schedule
    where campus like '%oshawa' and (not location = 'TBA')
    group by campus, location""")
db.commit()

db.close()

# Build indexes
build_starttimes(dbfile)
build_freerooms(dbfile, semester)
